﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using UtilZ.Dotnet.Ex.Base;

namespace UtilZ.Dotnet.DBIBase
{
    /// <summary>
    /// 数据库辅助类
    /// </summary>
    public static class DBHelper
    {
        /// <summary>
        /// 数据库字段对应的公共运行时类型枚举集合[key:数据库字段对应的公共运行时类型枚举;value:该类型对应的CLR类型集合]
        /// </summary>
        private static Dictionary<DBFieldType, List<Type>> _dicFieldClrDbTypes = null;

        /// <summary>
        /// 获取数据库字段对应的公共运行时类型[默认为DbClrFieldType.Other]
        /// </summary>
        /// <param name="fieldDataType">字段数据类型</param>
        /// <returns>数据库字段对应的公共运行时类型</returns>
        public static DBFieldType GetDbClrFieldType(Type fieldDataType)
        {
            if (_dicFieldClrDbTypes == null)
            {
                _dicFieldClrDbTypes = GetFieldClrDbTypes();
            }

            foreach (var kv in _dicFieldClrDbTypes)
            {
                if (kv.Value.Contains(fieldDataType))
                {
                    return kv.Key;
                }
            }

            return DBFieldType.Other;
        }

        /// <summary>
        /// 获取数据库字段对应的公共运行时类型枚举集合
        /// </summary>
        /// <returns>数据库字段对应的公共运行时类型枚举集合</returns>
        private static Dictionary<DBFieldType, List<Type>> GetFieldClrDbTypes()
        {
            /**********************************************
             *SQLServer:有如下几种类型不支持
             Microsoft.SqlServer.Types.SqlGeography
             Microsoft.SqlServer.Types.SqlGeometry
             Microsoft.SqlServer.Types.SqlHierarchyId
            ***********************************************/

            var dicFieldClrDbTypes = new Dictionary<DBFieldType, List<Type>>();

            //数值类型
            List<Type> numTypes = new List<Type>();
            numTypes.Add(TypeEx.BoolType);
            numTypes.Add(TypeEx.ByteType);
            numTypes.Add(TypeEx.DecimalType);
            numTypes.Add(TypeEx.DoubleType);
            numTypes.Add(TypeEx.Int16Type);
            numTypes.Add(TypeEx.Int32Type);
            numTypes.Add(TypeEx.Int64Type);
            numTypes.Add(TypeEx.SByteType);
            numTypes.Add(TypeEx.FloatType);
            numTypes.Add(TypeEx.UInt16Type);
            numTypes.Add(TypeEx.UInt32Type);
            numTypes.Add(TypeEx.UInt64Type);
            dicFieldClrDbTypes.Add(DBFieldType.Number, numTypes);

            //二进制类型
            List<Type> binaryTypes = new List<Type>();
            binaryTypes.Add(TypeEx.ByteAarrayType);
            dicFieldClrDbTypes.Add(DBFieldType.Binary, binaryTypes);

            //日期时间类型
            List<Type> dateTypes = new List<Type>();
            dateTypes.Add(TypeEx.DateTimeType);
            dateTypes.Add(TypeEx.TimeSpanType);
            dateTypes.Add(TypeEx.DateTimeOffsetType);
            dicFieldClrDbTypes.Add(DBFieldType.DateTime, dateTypes);

            //字符串类型
            List<Type> stringTypes = new List<Type>();
            stringTypes.Add(TypeEx.StringType);
            stringTypes.Add(TypeEx.GuidType);
            dicFieldClrDbTypes.Add(DBFieldType.String, stringTypes);

            return dicFieldClrDbTypes;
        }






        /// <summary>
        /// 转换ExecuteScalar结果为指定对象
        /// </summary>
        /// <typeparam name="T">目标类型</typeparam>
        /// <param name="obj">ExecuteScalar结果</param>
        /// <returns>转换结果</returns>
        public static T ConvertObject<T>(object obj)
        {
            if (obj == null || obj == DBNull.Value)
            {
                return default;
            }

            T result;
            Type conversionType = typeof(T);
            if (obj.GetType() == conversionType)
            {
                result = (T)obj;
            }
            else
            {
                result = (T)Convert.ChangeType(obj, conversionType);
            }

            return result;
        }

        /// <summary>
        /// 设置命令配置参数
        /// </summary>
        /// <param name="cmd">命令对象</param>
        /// <param name="config">配置</param>
        public static void SetCommandPara(IDbCommand cmd, DatabaseConfig config)
        {
            if (config.CommandTimeout != DBConstant.CommandTimeout)
            {
                cmd.CommandTimeout = config.CommandTimeout;
            }
        }

        /// <summary>
        /// 快速创建命令
        /// </summary>
        /// <param name="dbConnectionInfo">连接对象</param>
        /// <returns>命令</returns>
        public static IDbCommand CreateCommand(IDbConnectionInfo dbConnectionInfo)
        {
            var config = DatabaseConfigManager.GetConfig(dbConnectionInfo.DBID);
            var cmd = dbConnectionInfo.DbConnection.CreateCommand();
            SetCommandPara(cmd, config);
            return cmd;
        }

        /// <summary>
        /// 快速创建命令
        /// </summary>
        /// <param name="dbid">数据库ID</param>
        /// <param name="con">连接对象</param>
        /// <returns>命令</returns>
        public static IDbCommand CreateCommand(int dbid, IDbConnection con)
        {
            var config = DatabaseConfigManager.GetConfig(dbid);
            var cmd = con.CreateCommand();
            SetCommandPara(cmd, config);
            return cmd;
        }

        /// <summary>
        /// 快速创建命令
        /// </summary>
        /// <param name="dbAccess">IDBAccess</param>
        /// <param name="con">连接对象</param>
        /// <param name="parameterNameValueDic">参数名名称及对应的值字典集合[key:参数名称,含参数符号;value:参数值]</param>
        /// <returns>命令</returns>
        public static IDbCommand CreateCommand(this IDBAccess dbAccess, IDbConnection con, Dictionary<string, object> parameterNameValueDic = null)
        {
            var cmd = con.CreateCommand();
            if (parameterNameValueDic != null && parameterNameValueDic.Count > 0)
            {
                foreach (var kv in parameterNameValueDic)
                {
                    cmd.AddParameter(kv.Key, kv.Value);
                }
            }

            var config = dbAccess.Config;
            SetCommandPara(cmd, config);
            return cmd;
        }

        /// <summary>
        /// 快速创建命令
        /// </summary>
        /// <param name="dbAccess">IDBAccess</param>
        /// <param name="con">连接对象</param>
        /// <param name="sqlStr">SQL语句</param>
        /// <param name="parameterNameValueDic">参数名名称及对应的值字典集合[key:参数名称,含参数符号;value:参数值]</param>
        /// <param name="transaction">事务对象</param>
        /// <returns>命令</returns>
        public static IDbCommand CreateCommand(this IDBAccess dbAccess, IDbConnection con, string sqlStr,
            Dictionary<string, object> parameterNameValueDic = null, IDbTransaction transaction = null)
        {
            IDbCommand cmd = dbAccess.CreateCommand(con, parameterNameValueDic);
            cmd.Transaction = transaction;
            cmd.CommandText = sqlStr;
            return cmd;
        }

        /// <summary>
        /// 扩展方法添加命令参数
        /// </summary>
        /// <param name="cmd">目标命令</param>
        /// <param name="parameterName">参数名</param>
        /// <param name="value">参数值</param>
        public static void AddParameter(this IDbCommand cmd, string parameterName, object value)
        {
            var parameter = cmd.CreateParameter();
            parameter.ParameterName = parameterName;
            parameter.Value = value;
            cmd.Parameters.Add(parameter);
        }

        /// <summary>
        /// 扩展方法添加命令参数
        /// </summary>
        /// <param name="cmd">目标命令</param>
        /// <param name="parameterNameValueDic">参数字典集合[key:参数名;value:参数值]</param>
        public static void AddParameter(this IDbCommand cmd, Dictionary<string, object> parameterNameValueDic)
        {
            if (parameterNameValueDic == null || parameterNameValueDic.Count == 0)
            {
                return;
            }

            foreach (var kv in parameterNameValueDic)
            {
                var parameter = cmd.CreateParameter();
                parameter.ParameterName = kv.Key;
                parameter.Value = kv.Value;
                cmd.Parameters.Add(parameter);
            }
        }

        /// <summary>
        /// 创建命令参数
        /// </summary>
        /// <param name="cmd">所属命令对象</param>
        /// <param name="parameterName">参数名称</param>
        /// <returns>命令参数</returns>
        public static IDbDataParameter CreateParameter(this IDbCommand cmd, string parameterName)
        {
            var parameter = cmd.CreateParameter();
            parameter.ParameterName = parameterName;
            cmd.Parameters.Add(parameter);
            return parameter;
        }

        /// <summary>
        /// 创建命令参数列表
        /// </summary>
        /// <param name="dbAccess">IDBAccess</param>
        /// <param name="parameterNameValueDic">参数字典[Key:参数名称;Value:参数值]</param>
        /// <returns>命令参数</returns>
        public static List<IDbDataParameter> CreateParameterList(IDBAccess dbAccess, Dictionary<string, object> parameterNameValueDic)
        {
            DbProviderFactory dbProviderFactory = dbAccess.DBInteraction.GetProviderFactory();
            List<IDbDataParameter> parameterList = new List<IDbDataParameter>();
            if (parameterNameValueDic == null || parameterNameValueDic.Count == 0)
            {
                return parameterList;
            }

            foreach (var kv in parameterNameValueDic)
            {
                DbParameter dbParameter = dbProviderFactory.CreateParameter();
                dbParameter.ParameterName = kv.Key;
                dbParameter.Value = kv.Value;
                parameterList.Add(dbParameter);
            }

            return parameterList;
        }





        /// <summary>
        /// 扩展方法添加命令参数
        /// </summary>
        /// <param name="dbAccess">IDBAccess</param>
        /// <param name="cmd">目标命令</param>
        /// <param name="parameterName">参数名</param>
        /// <param name="value">参数值</param>
        public static void AddParameter(this IDBAccess dbAccess, DbBatchCommand cmd, string parameterName, object value)
        {
            DbProviderFactory dbProviderFactory = dbAccess.DBInteraction.GetProviderFactory();
            DbParameter parameter = dbProviderFactory.CreateParameter();
            parameter.ParameterName = parameterName;
            parameter.Value = value;
            cmd.Parameters.Add(parameter);
        }

        /// <summary>
        /// 扩展方法添加命令参数
        /// </summary>
        /// <param name="dbAccess">IDBAccess</param>
        /// <param name="cmd">目标命令</param>
        /// <param name="parameterNameValueDic">参数字典集合[key:参数名;value:参数值]</param>
        public static void AddParameter(this IDBAccess dbAccess, DbBatchCommand cmd, Dictionary<string, object> parameterNameValueDic)
        {
            if (parameterNameValueDic == null || parameterNameValueDic.Count == 0)
            {
                return;
            }

            DbProviderFactory dbProviderFactory = dbAccess.DBInteraction.GetProviderFactory();
            foreach (var kv in parameterNameValueDic)
            {
                DbParameter parameter = dbProviderFactory.CreateParameter();
                parameter.ParameterName = kv.Key;
                parameter.Value = kv.Value;
                cmd.Parameters.Add(parameter);
            }
        }





        /*
        #region 生成条件SQL
        /// <summary>
        /// 生成Oracle无参数SQL语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="paraSign">参数符号</param>
        /// <param name="conditionGroupCollection">条件组集合</param>
        /// <param name="queryFields">要查询的字段名称集合,为null或空时全查</param>
        /// <returns>Oracle无参数SQL语句</returns>
        public static string GenerateNoParaOracleSql(string tableName, string paraSign, ConditionGroupCollection conditionGroupCollection, List<string> queryFields = null)
        {
            var conditionGenerator = new OracleConditionGenerator(tableName, paraSign, conditionGroupCollection, 0, queryFields);
            return conditionGenerator.GenerateNoParaConditionOracleSql();
        }

        /// <summary>
        /// 生成Oracle无参数SQL语句
        /// </summary>
        /// <param name="conditionGroupCollection">条件组集合</param>
        /// <returns>Oracle无参数SQL语句</returns>
        public static string GenerateNoParaOracleWhere(ConditionGroupCollection conditionGroupCollection)
        {
            var conditionGenerator = new OracleConditionGenerator(null, null, conditionGroupCollection, 0, null);
            return conditionGenerator.GenerateNoParaConditionOracleWhere();
        }

        /// <summary>
        /// 生成Oracle带参数SQL语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="paraSign">参数符号</param>
        /// <param name="conditionGroupCollection">条件组集合</param>
        /// <param name="paraIndex">参数索引</param>
        /// <param name="queryFields">要查询的字段名称集合,为null或空时全查</param>
        /// <returns>Oracle带参数SQL语句对象</returns>
        public static DBSqlInfo GenerateParaSql(string tableName, string paraSign, ConditionGroupCollection conditionGroupCollection, IEnumerable<string> queryFields = null)
        {
            var conditionGenerator = new OracleConditionGenerator(tableName, paraSign, conditionGroupCollection, 1, queryFields);
            return conditionGenerator.GenerateParaSql();
        }

        /// <summary>
        /// 生成Oracle带参数SQL语句
        /// </summary>
        /// <param name="paraSign">参数符号</param>
        /// <param name="conditionGroupCollection">条件组集合</param>
        /// <param name="paraIndex">参数索引</param>
        /// <returns>Oracle带参数SQL语句对象</returns>
        public static DBSqlInfo GenerateParaWhere(string paraSign, ConditionGroupCollection conditionGroupCollection, ref int paraIndex)
        {
            var conditionGenerator = new OracleConditionGenerator(null, paraSign, conditionGroupCollection, paraIndex, null);
            DBSqlInfo dbWhereInfo = conditionGenerator.GenerateParaWhere();
            paraIndex = conditionGenerator.ParaIndex;
            return dbWhereInfo;
        }

        /// <summary>
        /// 生成Oracle带参数SQL语句
        /// </summary>
        /// <param name="paraSign">参数符号</param>
        /// <param name="conditionGroupCollection">条件组集合</param>
        /// <returns>Oracle带参数SQL语句对象</returns>
        public static DBSqlInfo GenerateParaWhere(string paraSign, ConditionGroupCollection conditionGroupCollection)
        {
            var conditionGenerator = new OracleConditionGenerator(null, paraSign, conditionGroupCollection, 1, null);
            DBSqlInfo dbWhereInfo = conditionGenerator.GenerateParaWhere();
            return dbWhereInfo;
        }
        #endregion
    */
    }
}
